#include "databaseoperator.h"

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QFile>
#include <QDebug>

DatabaseOperator * DatabaseOperator::unique_instance = nullptr;

DatabaseOperator * DatabaseOperator::getInstance()
{
    if (unique_instance == nullptr) {
        unique_instance = new DatabaseOperator;
    }
    return unique_instance;
}

DatabaseOperator::~DatabaseOperator()
{
    QSqlDatabase::removeDatabase("InputMethod");
}

bool DatabaseOperator::openDatabaseFile(const QString &filePath, const QString &passwd)
{
    if (!QFile::exists(filePath)) {
        return false;
    }
    if (QSqlDatabase::database("InputMethod").isOpen())
    {
        return true;
    }
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "InputMethod");
    db.setDatabaseName(filePath);
    db.setPassword(passwd);
    return db.open();
}

QStringList DatabaseOperator::findData(const QString &key, const QString &number, const QString & table)
{
    QStringList list;
    QSqlQuery query(QSqlDatabase::database("InputMethod"));
    QString table_fact = table;

    if (table_fact == "basePinyin" && !key.isEmpty()) {
        if (!key.at(0).isLetter() || number.toInt()> 6 ) {
            return list;
        }
        else {
            table_fact += QString("_%1").arg(key.at(0).toUpper());
        }
    }
    QString sqlcmd = QString("SELECT id, %1, %2, extra, times, stick FROM %3 "
                             "WHERE %4 like \"%5\" AND extra like \"%6\" "
                             "ORDER BY times DESC LIMIT 0,%7;")
            .arg("pinyin")
            .arg("chinese")
            .arg(table_fact)
            .arg("pinyin")
            .arg(key)
            .arg(number)
            .arg(200);
    bool ok = query.exec(sqlcmd);
    if (!ok) {
        qWarning("error: %s", query.lastError().text().toUtf8().data());
    }
    while (query.next()) {
        list << QString(query.value(2).toString().trimmed());
    }
    return list;
}

bool DatabaseOperator::updateData(const QString &pinyin, const QString &word)
{
    QStringList tablelsit;
    tablelsit << QString("basePinyin_") + pinyin.at(0).toUpper()
              << "userPinyin";
    //尝试更新该词的times
    for (auto table : tablelsit) {
        if (updateTimes(pinyin, word, table)) {
            return true;
        }
    }
    //不更新单字记录，可能引起问题
//    if (word.size() == 1) {
//        updateTimes(pinyin, "%"+word+"%", "singlePinyin");
//        return true;
//    }
    //未更新（没查找到）则加入userPinyin
    QSqlQuery query(QSqlDatabase::database("InputMethod"));
    QString sql_insert_cmd = QString("INSERT INTO userPinyin (pinyin, chinese, extra, times, stick)"
                                     "VALUES (\"%1\", \"%2\", \"%3\", 1, 0);")
            .arg(pinyin)
            .arg(word)
            .arg(word.size());
    return query.exec(sql_insert_cmd);
}

bool DatabaseOperator::updateTimes(const QString &pinyin, const QString &word, const QString &table)
{
    // 查找词组是否存在
    QSqlQuery query(QSqlDatabase::database("InputMethod"));
    QString sql_select_cmd = QString("SELECT id, pinyin, chinese, extra, times, stick FROM %1 "
                                     "WHERE pinyin like \"%2\" AND chinese like \"%3\";")
            .arg(table)
            .arg(pinyin)
            .arg(word);
    if (!query.exec(sql_select_cmd)) {
        qDebug() << "Select Error:" << query.lastError().text().toUtf8().data();
    }
    int id = -1, times = -1;
    while (query.next()) {
        id = query.value(0).toInt();
        times = query.value(4).toInt();
    }
    // 存在则times+=1
    if (id != -1) {
        QString sql_update_cmd = QString("UPDATE %1 SET times = %2 WHERE ID=%3;")
                .arg(table)
                .arg(times+1)
                .arg(id);
        if (!query.exec(sql_update_cmd)) {
            qDebug() << "Update Error:" << query.lastError().text().toUtf8().data();
        }
        return true;
    }
    return false;
}

bool DatabaseOperator::deleteUserTable()
{
    QSqlQuery query(QSqlDatabase::database("InputMethod"));
    return query.exec("DELETE FROM userPinyin;");
}

bool DatabaseOperator::resetAllTimes()
{
    QSqlQuery query(QSqlDatabase::database("InputMethod"));
    bool ok = query.exec(QString("UPDATE singlePinyin SET times = 1;"));
    for (int i = 'A'; i != 'Z'+1; ++i) {
        if (!query.exec(QString("UPDATE basePinyin_%1 SET times = 1;").arg(QChar(i)))) {
            ok = false;
        }
    }
    return ok;
}
